Public Class StatusReport

    Public Shared Function StatusReport(ByVal ds As DataSet) As DataSet
        Try

            Dim tblOptions As DataTable = ds.Tables("Options")
            Dim tblUser As DataTable = ds.Tables("Users")
            Dim tblAction As DataTable = ds.Tables("Action")
            Dim oReport As New Reports
            Dim rtds As New DataSet
            Dim PolicyNumber As String = ""
            rtds.Tables.Add(tblOptions.Clone)
            rtds.Tables.Add(New dsStatus.UserDataDataTable)
            rtds.Tables.Add(New dsStatus.SummaryActionDataTable)
            rtds.Tables.Add(New dsStatus.SummarySystemDataTable)

            For Each rowUser As DataRow In tblUser.Rows

                Dim drOptions As DataRow
                drOptions = rtds.Tables("Options").NewRow
                drOptions("UserID") = rowUser("User")
                drOptions("FromDate") = tblOptions.Rows(0).Item("FromDate")
                drOptions("ToDate") = tblOptions.Rows(0).Item("ToDate")
                rtds.Tables("Options").Rows.Add(drOptions)

                Dim dt As DataTable = oReport.GetUserWorkByContactMethod(rowUser("User"), _
                                            tblAction, _
                                            tblOptions.Rows(0).Item("FromDate"), _
                                            tblOptions.Rows(0).Item("ToDate"))

                For Each rowUserWork As DataRow In dt.Rows

                    'Dim iCol As Int16 = dt.Columns("Total Calls Made").Ordinal + 1
                    If PolicyNumber = rowUserWork("Policy_Number") Then
                        For Each CurrentRow As DataRow In rtds.Tables("UserData").Rows
                            If CurrentRow("Policy_Number") = PolicyNumber Then
                                CurrentRow(rowUserWork("ContactMethod")) += rowUserWork("CountOfContactMethod")
                                Exit For
                            End If
                        Next
                    Else
                        'User Work
                        Dim drUserWork As DataRow
                        drUserWork = rtds.Tables("UserData").NewRow
                        drUserWork("UserID") = rowUser("User").ToString.ToUpper
                        drUserWork("Letter_Code") = rowUserWork("Letter_Code").ToString.ToUpper
                        drUserWork("Action") = rowUserWork("Action")
                        drUserWork("Account_SYSTEM") = rowUserWork("Account_SYSTEM").ToString.ToUpper
                        drUserWork("NEW_PREMIUM_AMOUNT") = rowUserWork("NEW_PREMIUM_AMOUNT")
                        drUserWork("AMOUNT_Due") = rowUserWork("AMOUNT_Due")
                        drUserWork("WriteOff") = rowUserWork("WriteOff")
                        drUserWork("Retained") = rowUserWork("Retained")
                        drUserWork("Policy_Number") = rowUserWork("Policy_Number")
                        For iCtr As Int16 = rtds.Tables("UserData").Columns("Total Calls Made").Ordinal To rtds.Tables("UserData").Columns.Count - 1
                            drUserWork(iCtr) = 0
                        Next
                        drUserWork("Total Calls Made") = 1
                        drUserWork(rowUserWork("ContactMethod")) = rowUserWork("CountOfContactMethod")
                        rtds.Tables("UserData").Rows.Add(drUserWork)

                        PolicyNumber = rowUserWork("Policy_Number")
                    End If
                    '*************************************************************
                    ' SummaryAction
                    Dim bActionUpdated As Boolean = False
                    For Each drSummary As DataRow In rtds.Tables("SummaryAction").Rows
                        If (drSummary("Action") = rowUserWork("Action")) And (drSummary("UserID").ToString.ToUpper = rowUserWork("User").ToString.ToUpper) Then
                            drSummary("ActionCount") += 1
                            drSummary("ActionNEW_PREMIUM_AMOUNT") += rowUserWork("NEW_PREMIUM_AMOUNT")
                            drSummary("ActionAmount_Due") += rowUserWork("Amount_Due")
                            drSummary("ActionRetained") += rowUserWork("Retained")
                            bActionUpdated = True
                            Exit For
                        End If
                    Next ' Summary
                    If Not bActionUpdated Then
                        Dim drUserSummary As DataRow
                        drUserSummary = rtds.Tables("SummaryAction").NewRow
                        drUserSummary("UserID") = rowUser("User").ToString.ToUpper
                        drUserSummary("ToDate") = tblOptions.Rows(0).Item("FromDate")
                        drUserSummary("FromDate") = tblOptions.Rows(0).Item("ToDate")
                        drUserSummary("Action") = rowUserWork("Action")
                        drUserSummary("ActionCount") = 1
                        drUserSummary("ActionNEW_PREMIUM_AMOUNT") = rowUserWork("NEW_PREMIUM_AMOUNT")
                        drUserSummary("ActionAmount_Due") = rowUserWork("Amount_Due")
                        drUserSummary("ActionRetained") = rowUserWork("Retained")
                        rtds.Tables("SummaryAction").Rows.Add(drUserSummary)
                    End If

                    '*************************************************************
                    ' SummarySystem
                    Dim bSystemUpdated As Boolean = False
                    For Each drSummary As DataRow In rtds.Tables("SummarySystem").Rows
                        If (drSummary("Account_SYSTEM").ToString.ToUpper = rowUserWork("Account_SYSTEM").ToString.ToUpper) And (drSummary("UserID").ToString.ToUpper = rowUserWork("User").ToString.ToUpper) Then
                            drSummary("Account_SYSTEMCount") += 1
                            drSummary("Account_SYSTEMNEW_PREMIUM_AMOUNT") += rowUserWork("NEW_PREMIUM_AMOUNT")
                            drSummary("Account_SYSTEMAmount_Due") += rowUserWork("Amount_Due")
                            drSummary("Account_SYSTEMRetained") += rowUserWork("Retained")
                            bSystemUpdated = True
                            Exit For
                        End If
                    Next ' Summary
                    If Not bSystemUpdated Then
                        Dim drUserSummary As DataRow
                        drUserSummary = rtds.Tables("SummarySystem").NewRow
                        drUserSummary("UserID") = rowUser("User").ToString.ToUpper
                        drUserSummary("ToDate") = tblOptions.Rows(0).Item("FromDate")
                        drUserSummary("FromDate") = tblOptions.Rows(0).Item("ToDate")
                        drUserSummary("Account_SYSTEM") = rowUserWork("Account_SYSTEM").ToString
                        drUserSummary("Account_SYSTEMCount") = 1
                        drUserSummary("Account_SYSTEMNEW_PREMIUM_AMOUNT") = rowUserWork("NEW_PREMIUM_AMOUNT")
                        drUserSummary("Account_SYSTEMAmount_Due") = rowUserWork("Amount_Due")
                        drUserSummary("Account_SYSTEMRetained") = rowUserWork("Retained")
                        rtds.Tables("SummarySystem").Rows.Add(drUserSummary)
                    End If
                Next ' Work

            Next 'User 

            Return rtds
        Catch ex As Exception
            Throw ex
        End Try
    End Function
End Class
